package model.dba;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Curso;

public class CursoDAO extends JDBC {
	private static CursoDAO cursoDAO;

	private CursoDAO() {
		super();
	}

	public static CursoDAO getInstance() {
		if (cursoDAO == null) {
			cursoDAO = new CursoDAO();
		}
		return cursoDAO;
	}

	public Integer add(Curso curso) {

		try {
			String queryString = "INSERT INTO cursos(nombre, descripcion , horasTotales ,	temario , idarea) " + " VALUES(?,?,?,?,?) ";

			preparedStatement = connection.prepareStatement(queryString);
			preparedStatement.setString(1, curso.getNombre());
			preparedStatement.setString(2, curso.getDescripcion());
			preparedStatement.setLong(3, curso.getHorasTotales());
			preparedStatement.setString(4, curso.getTemario());
			preparedStatement.setLong(5, curso.getIdArea());

			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return getLastId();

	}

	public boolean update(Curso curso) {
		boolean state = false;
		try {
			String queryString = "UPDATE cursos SET  nombre=?, descripcion=?, horasTotales=?, temario=?, idarea=?, eliminado=?  WHERE idCurso=?";

			preparedStatement = connection.prepareStatement(queryString);

			preparedStatement.setString(1, curso.getNombre());
			preparedStatement.setString(2, curso.getDescripcion());
			preparedStatement.setLong(3, curso.getHorasTotales());
			preparedStatement.setString(4, curso.getTemario());
			preparedStatement.setLong(5, curso.getIdArea());
			preparedStatement.setString(6, curso.getEliminado());
			preparedStatement.setInt(7, curso.getId());
			state = preparedStatement.executeUpdate() != 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			}

			catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();

			}
		}
		return state;
	}

	public boolean delete(Curso curso) {
		boolean state = false;

		try {
			String queryString = "DELETE FROM cursos WHERE idCurso=?";

			preparedStatement = connection.prepareStatement(queryString);
			preparedStatement.setInt(1, curso.getId());
			state = preparedStatement.executeUpdate() != 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return state;

	}

	public Curso get(Integer id) {

		Curso ret = new Curso();
		try {
			String queryString = "SELECT * FROM cursos where idCurso = '" + id + "';";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				ret = populate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return ret;
	}

	public int getLastId() {
		int ret = 0;
		try {
			String queryString = "SELECT idCurso FROM cursos order by idCurso DESC LIMIT 1  ";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				ret = resultSet.getInt("idCurso");

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return ret;

	}

	public List<Curso> getAll() {
		List<Curso> cursoList = new ArrayList<Curso>();

		try {
			String queryString = "SELECT * FROM cursos ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				cursoList.add(populate());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return cursoList;
	}

	// public List<Curso> get(int idarea) {
	// List<Curso> cursoList = new ArrayList<Curso>();
	//
	// try {
	// String queryString = "SELECT * FROM cursos WHERE idarea= " + idarea + " ;";
	//
	// preparedStatement = connection.prepareStatement(queryString);
	// resultSet = preparedStatement.executeQuery();
	// while (resultSet.next()) {
	//
	// cursoList.add(populate());
	// }
	// } catch (SQLException e) {
	// e.printStackTrace();
	// } finally {
	// try {
	// if (resultSet != null)
	// resultSet.close();
	// if (preparedStatement != null)
	// preparedStatement.close();
	// } catch (SQLException e) {
	// e.printStackTrace();
	// } catch (Exception e) {
	// e.printStackTrace();
	// }
	//
	// }
	// return cursoList;
	// }

	public List<Curso> get(int idarea) {
		List<Curso> cursoList = new ArrayList<Curso>();

		try {
			String queryString = "SELECT * FROM cursos WHERE idarea= " + idarea + " ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				cursoList.add(populate());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return cursoList;
	}

		Curso curso = new Curso();
		public Curso get(String nombre) {
		try {
			// nombre = nombre != null ? nombre.trim() : "";
			String queryString = "SELECT * FROM cursos WHERE nombre= '" + nombre + "' ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				curso = populate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return curso;
	}

	private Curso populate() throws SQLException {

		// AreaDAO areaDAO = AreaDAO.getInstance();
		// List<Area> areas = new ArrayList<Area>();
		Curso curso = new Curso();
		// areas.add(areaDAO.get((int) resultSet.getLong("idarea")));
		// curso.setAreas(areas);
		curso.setDescripcion(resultSet.getString("descripcion"));
		curso.setHorasTotales((int) resultSet.getLong("horasTotales"));
		curso.setId((int) resultSet.getLong("idCurso"));
		curso.setTemario(resultSet.getString("temario"));
		curso.setNombre(resultSet.getString("nombre"));
		curso.setIdArea((int) resultSet.getLong("idarea"));
		curso.setEliminado(resultSet.getString("eliminado"));

		return curso;

	}
}